﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace InsideDL.MISData
{
    public class DataFTPRateInterest:DataMatrix
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_ctr">截取版塊→1表公庫版塊 、A表財管版塊、C D E表企金版塊</param>
        /// <param name="p_connect">完整路徑+檔名</param>
        /// <returns></returns>
        public object GetEIS(string p_ctr, string p_connect)
        {
            return getExcelDataByRecordSet(getEISSQL(p_ctr), p_connect);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_ctr">'1','A','C','D','E'</param>
        /// <returns></returns>
        protected string getEISSQL(string p_ctr)
        {
            return string.Format(@"
        SELECT
            LEFT(COST_CTR,3) AS COS
            ,MID(COST_CTR,4,1) AS T
            ,RIGHT(COST_CTR,1) AS [_CTR]
            ,GL_TYPE 
            ,GL_ACCT
            ,CURRENCY_CODE
            ,IE_BAL總計
            ,IE_INT總計
            ,IE_TRANS總計
        FROM [DAT$C5:J60000]
        WHERE MID(COST_CTR,4,1) IN ({0})
        ",p_ctr);

        }

        public object GetMargin(string p_margin, string p_connect)
        {
            return getExcelDataByRecordSet(getMarginSQL(), p_connect);
        }

        protected string getMarginSQL()
        {
            return string.Format(@"
        SELECT 
            板塊 
            ,LEFT([GL Account Number],10) AS [科子細目]
            ,MID([GL Account Number],11) AS [名稱]
            ,[Average Balance]
            ,[Interest  Rate]
            ,[Transfer Rate]
        FROM [L_all$B5:J2000]
        WHERE 板塊 IN ('1','A','C')
        AND [GL Account Number] <> 'Liability Accounts:'
        AND [GL Account Number] <> 'Totals for selected Liability Accounts'
        AND [GL Account Number] <> 'Asset Accounts:'
        AND [GL Account Number] <> 'Totals for selected Asset Accounts'
        AND [GL Account Number] NOT IN('1','A','C')
        ");

        }
    }
}
